﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Reflection;
using System.Text;
using Dataglot.Utils;

namespace Dataglot
{
    /// <summary>
    /// A CRUD class for tables with an Id.
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <remarks>
    /// Override all methods, then call the corresponding xxxProcess methods.
    /// 
    /// The data transfer objects must support an numberic primary key, paramterless contructor
    /// and a Validate method that throws exceptions when the DTO is invalid.
    /// </remarks>
    public abstract class TableController<T>:IDisposable where T : ISupportId, new()
    {
        private readonly bool needToDispose;

        protected readonly DataFactory factory;

        protected TableController(DataFactory targetFactory, DbConnection connection, DbTransaction transaction)
        {
            factory = targetFactory;

            if(!(connection==null && transaction==null))
            {
                TraceApp.Verbose(() => "Table controller is using shared connection");    
                sharedConnection = connection;
                sharedTransaction = transaction;
                needToDispose = false;
            }
            else
            {
                TraceApp.Verbose(() => "Table controller is creating own connection");
                sharedConnection = targetFactory.CreateOpenConnection();
                sharedTransaction = sharedConnection.BeginTransaction();
                needToDispose = true;
            }

        }

        protected string tableName;
        protected Dictionary<string,DbType> columns;

        protected string exitsSql = "select 1 from {table_name} where id = @id ";
        protected string selectSql = "select ... from {table_name} where id = @id ";
        protected string selectListSql = "select ... from {table_name} where id > @from and id <= @to ";
        protected string selectListSimpleSql = "select ... from {table_name}";
        protected string updateSql = "update {table_name} set ... where id = @id ";
        protected string insertSql = "insert into {table_name} (...) values ( @{values} )";
        protected string deleteSql = "delete from {table_name} where id = @id ";

        protected GenericCommand existsCommand;
        protected GenericCommand selectCommand;
        protected GenericCommand selectListCommand;
        protected GenericCommand selectListSimpleCommand;
        protected GenericCommand updateCommand;
        protected GenericCommand insertCommand;
        protected GenericCommand deleteCommand;

        public abstract T Select(long id);

        protected DbConnection sharedConnection;
        protected DbTransaction sharedTransaction;

        public abstract bool Exists(long id);

        protected bool ProcessExists(long id)
        {
            if (id == 0)
                throw new InvalidOperationException("Id of 0 is invalid");

            if (existsCommand==null || !existsCommand.IsInitialized())
            {
                string sql = exitsSql.Replace("{table_name}", tableName);

                if (sharedConnection == null)
                    throw new InvalidOperationException("sharedConnection must be set before attempting to process any command");
                existsCommand = new GenericCommand(factory,sharedConnection, sharedTransaction);
                existsCommand.AddParameter("id", DbType.Int64);
                existsCommand.ComposeSql(sql);
            }
            existsCommand.Set("id", id);

            using (DbDataReader reader = existsCommand.ExecuteReader(CommandBehavior.SingleRow))
            {
                //SqlCompact doesn't support HasRows unless it is executed with SqlCompact specific commmands.
                //if(reader.HasRows)
                while(reader.Read())
                {
                    return true;
                }
            }
            return false;
        }

        protected T ProcessSelect(long id)
        {
            if(id==0)
                throw new InvalidOperationException("Id of 0 is invalid");

            if (tableName == null)
                throw new InvalidOperationException("tableName needs to be set");

            T dto = new T();
            dto.Id = id;

            if(selectCommand==null|| !selectCommand.IsInitialized())
            {
                string sql = selectSql.Replace("...", string.Join(", ", columns.Keys.ToArray())).Replace("{table_name}",tableName);

                if(sharedConnection==null)
                    throw new InvalidOperationException("sharedConnection must be set before attempting to process any command");
                selectCommand = new GenericCommand(factory, sharedConnection, sharedTransaction);
                selectCommand.AddParameter("id", DbType.Int64);
                selectCommand.ComposeSql(sql);
            }
            selectCommand.Set("id", id);
            
            using (DbDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow))
            {
                //HasRows property not supported well on SqlCompact.
                bool hasRows= reader.Read();
                if (!hasRows)
                {
                    throw new InvalidOperationException("Failed to find a row with id of " + id);
                }

                //dto.Name = reader.GetValue(reader.GetOrdinal("name")).ToString();

                Type currentType = ExtractBaseType(dto);

                PropertyInfo[] props = currentType.GetProperties(BindingFlags.Public | BindingFlags.Instance);

                foreach (PropertyInfo prop in props)
                {
                    if (null != prop && prop.CanWrite)
                    {
                        if (prop.PropertyType.IsEnum)
                        {
                            //If null, then leave prop at default value according to how C# initializes properties
                            if (!reader.IsDBNull(reader.GetOrdinal(NameUtils.SplitParameterWordsWithUnderscore(prop.Name))))
                            {
                                prop.SetValue(dto,
                                              Enum.Parse(prop.PropertyType,
                                                         reader.GetValue(reader.GetOrdinal(NameUtils.SplitParameterWordsWithUnderscore(prop.Name))).
                                                             ToString()), null);
                            }
                        }
                        else
                        {
                            //If null, then leave prop at default value according to how C# initializes properties
                            if (!reader.IsDBNull(reader.GetOrdinal(NameUtils.SplitParameterWordsWithUnderscore(prop.Name))))
                            {
                                prop.SetValue(dto, reader.GetValue(reader.GetOrdinal(NameUtils.SplitParameterWordsWithUnderscore(prop.Name))), null);
                            }
                        }
                        
                    }
                }
            }
            dto.ValidateRequirements();
            return dto;
        }

        /// <summary>
        /// This will discard the first result! Don't use!
        /// </summary>
        private bool HasRows(DbDataReader reader)
        {
            while(reader.Read())
            {
                return true;
            }
            return false;
        }
        
        public abstract List<T> SelectList(int from, int to);

        protected List<T> ProcessSelectList(int from, int to)
        {
            if(from >0 && to>0)
            {
                TraceApp.Verbose(() => "Processing a range");    
                return RangedList(from, to);
            }
            else
            {
                TraceApp.Verbose(() => "Processing a entire list");    
                return SimpleList();
            }
        }

        private List<T> SimpleList()
        {
            if (selectListSimpleCommand == null || !selectListSimpleCommand.IsInitialized())
            {
                string sql =
                    selectListSimpleSql.Replace("...", string.Join(", ", columns.Keys.ToArray())).Replace("{table_name}",
                                                                                                    tableName);

                if (sharedConnection == null)
                    throw new InvalidOperationException(
                        "sharedConnection must be set before attempting to process any command");
                selectListSimpleCommand = new GenericCommand(factory, sharedConnection, sharedTransaction);
                selectListSimpleCommand.ComposeSql(sql);
            }

            using (DbDataReader reader = selectListSimpleCommand.ExecuteReader(CommandBehavior.SingleResult))
            {
                return BindList(reader);
            }

        }

        private List<T> RangedList(int fromId, int toId)
        {

            if (selectListCommand == null || !selectListCommand.IsInitialized())
            {
                string sql =
                    selectListSql.Replace("...", string.Join(", ", columns.Keys.ToArray())).Replace("{table_name}",
                                                                                                    tableName);

                if (sharedConnection == null)
                    throw new InvalidOperationException(
                        "sharedConnection must be set before attempting to process any command");
                selectListCommand = new GenericCommand(factory, sharedConnection, sharedTransaction);
                selectListCommand.AddParameter("fromId", DbType.Int64);
                selectListCommand.AddParameter("toId", DbType.Int64);
                selectListCommand.ComposeSql(sql);
            }
            selectListCommand.Set("fromId", fromId);
            selectListCommand.Set("toId", toId);

            using (DbDataReader reader = selectListCommand.ExecuteReader(CommandBehavior.SingleResult))
            {
                return BindList(reader);
            }
        }

        private List<T> BindList(DbDataReader reader)
        {
            List<T> list = new List<T>();
            //For Sql Ce, you can only call HasRows if you use this to create the reader
            //SqlCeDataReader dr = command.ExecuteResultSet(ResultSetOptions.Scrollable);
            //if (!reader.HasRows)
            //{
            //    return list;
            //}

            while (reader.Read())
            {
                T dto = new T();

                PropertyInfo[] props = dto.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);

                foreach (PropertyInfo prop in props)
                {
                    if (null != prop && prop.CanWrite)
                    {
                        if (prop.PropertyType.IsEnum)
                        {

                            prop.SetValue(dto,
                                          Enum.Parse(prop.PropertyType,
                                                     reader.GetValue(reader.GetOrdinal(NameUtils.SplitParameterWordsWithUnderscore(prop.Name))).
                                                         ToString()), null);
                        }
                        else
                            prop.SetValue(dto, reader.GetValue(reader.GetOrdinal(NameUtils.SplitParameterWordsWithUnderscore(prop.Name))), null);
                    }
                }
                list.Add(dto);
            }

            return list;
        }
        

        public abstract T Insert(T setting);

        protected T ProcessInsert(T setting)
        {
            if (insertCommand==null || !insertCommand.IsInitialized())
            {

                string sql = insertSql.Replace("...", string.Join(", ", columns.Keys.Where(prop => prop.ToLower() != "id").ToArray()));
                if(tableName==null)
                    throw new InvalidOperationException("tableName needs to be set");
                sql = sql.Replace("{values}", string.Join(", @", columns.Keys.Where(prop => prop.ToLower() != "id").ToArray())).Replace("{table_name}", tableName);


                if (factory.SupportInfo.IsMsSql)
                {
                    sql += ";SELECT NEWID = SCOPE_IDENTITY();";
                }
                

                if (sharedConnection == null)
                    throw new InvalidOperationException("sharedConnection must be set before attempting to process any command");

                insertCommand = new GenericCommand(factory, sharedConnection, sharedTransaction);
                insertCommand.ComposeSql(sql);

                foreach (KeyValuePair<string, DbType> c in columns)
                {
                    if (c.Key.ToLower() != "id" )
                    {
                        insertCommand.AddParameter("@" + c.Key, c.Value);
                    }
                    else if (DoWeIncludeId(c.Key, factory))
                    {
                        insertCommand.AddParameter("@id", c.Value);
                    }
                }
            }

            Type currentType = ExtractBaseType(setting);

            PropertyInfo[] props = currentType.GetProperties(BindingFlags.Public | BindingFlags.Instance);


            foreach (PropertyInfo prop in props)
            {
                if (prop.Name.ToLower() != "id")
                {
                    if (IsSimpleType(prop.PropertyType))
                    {
                        insertCommand.Set("@" + NameUtils.SplitParameterWordsWithUnderscore(prop.Name), NullUtils.ConvertNullToDbNull(prop.GetValue(setting, null)));
                    }
                }
                else if (DoWeIncludeId(prop.Name,factory))
                {
                    //Maybe insert a random # & retry on collisions?
                    if (IsSimpleType(prop.PropertyType))
                    {
                        insertCommand.Set("@" + NameUtils.SplitParameterWordsWithUnderscore(prop.Name), NullUtils.ConvertNullToDbNull(prop.GetValue(setting, null)));
                    }
                }
            }

            setting.ValidateRequirements();

            
                object id = insertCommand.ExecuteScalar();

            //If client set it, assume they knew what they were doing.
            if (setting.Id == 0)
            {
                setting.Id = Convert.ToInt64(id);
            }
            if(factory.SupportInfo.Brand== DbBrand.SqlCompact4)
            {
                GenericCommand comm = new GenericCommand(factory,sharedConnection,sharedTransaction);
                comm.ComposeSql("select @@IDENTITY");
                object result = comm.ExecuteScalar();
                setting.Id = Convert.ToInt64(result);
            }
            //Reload to capture db defaults, etc.
            T dto = ProcessSelect(setting.Id);
            dto.ValidateRequirements();
            //sharedTransaction.Commit();
            return dto;
        }

        private bool DoWeIncludeId(string key, DataFactory dataFactory)
        {
            return key.ToLower() == "id" && !dataFactory.SupportInfo.SupportsIdentityColumn;
        }

        private Type ExtractBaseType(T setting)
        {
            Type possibleType = setting.GetType();
            Type actualType;
            if(possibleType.BaseType!=null  && possibleType.BaseType.ToString()!="System.Object")
            {
                actualType = possibleType.BaseType;
            }
            else
            {
                actualType = possibleType;
            }
            return actualType;
        }

        public abstract T Update(T setting);
        
        /// <summary>
        /// Update a single row of a single table. Makes best guess if the database supports auto ids
        /// </summary>
        /// <param name="setting"></param>
        /// <returns></returns>
        /// <remarks>
        ///  Three loops, one for the column name list, one for parameters, one for setting values of parameters
        /// </remarks>
        protected T ProcessUpdate(T setting)
        {
            if (setting.Id == 0)
                throw new InvalidOperationException("Id of 0 is invalid");

            if(!ProcessExists(setting.Id))
            {
                throw new InvalidOperationException("Row for id "+ setting.Id + " doesn't exist. Can't update.");
            }

            if (updateCommand==null || !updateCommand.IsInitialized())
            {
                //TODO: Execute 'error if not exists'
                StringBuilder sb = new StringBuilder();
                foreach (string column in columns.Keys)
                {
                    if ((column.ToLower() == "id" && factory.SupportInfo.IsMsSql))
                    {
                        TraceApp.Verbose(() => "Skipping adding the ID to the sql, probably because we think we are using SQL and the ID's are numeric auto id's");
                        continue;
                    }
                    sb.Append(column + " = @" + column + ",");
                }
                string columnsToUpdate = sb.ToString().Substring(0, sb.Length - 1);
                if (tableName == null)
                    throw new InvalidOperationException("tableName needs to be set");
                string sql = updateSql.Replace("...", columnsToUpdate).Replace("{table_name}", tableName);

                if (sharedConnection == null)
                    throw new InvalidOperationException("sharedConnection must be set before attempting to process any command");
                updateCommand = new GenericCommand(factory, sharedConnection, sharedTransaction);
                
                foreach (KeyValuePair<string, DbType> c in columns)
                {
                    //if ((c.Key.ToLower() == "id" && factory.IsMsSql))
                    //{
                    //    TraceApp.Verbose(() => "Skipping adding the ID param, probably because we think we are using SQL and the ID's are numeric auto id's");
                    //    continue;
                    //}
                    updateCommand.AddParameter("@" + c.Key, c.Value);
                }
                updateCommand.ComposeSql(sql);
            }

            Type currentType = ExtractBaseType(setting);
            PropertyInfo[] props = currentType.GetProperties(BindingFlags.Public | BindingFlags.Instance);


            foreach (PropertyInfo prop in props)
            {
                //if (prop.Name.ToLower() == "id" && factory.IsMsSql)
                //{
                //    TraceApp.Verbose(() => "Skipping setting ID, probably because we think we are using SQL and the ID's are numeric auto id's");
                //    continue;
                //}
                if (IsSimpleType(prop.PropertyType))
                {
                    updateCommand.Set("@" + NameUtils.SplitParameterWordsWithUnderscore(prop.Name), NullUtils.ConvertNullToDbNull(prop.GetValue(setting, null)));
                }   
            }

            int rowsAffected= updateCommand.ExecuteNonQuery();
            if(rowsAffected==0)
            {
                throw new InvalidOperationException("Expected a row to be updated, but none were.");
            }


            //TODO: Execute select
            T dto = ProcessSelect(setting.Id); 
            dto.ValidateRequirements();
            return dto;
        }

        public bool IsSimpleType(Type currentType)
        {
            if (currentType.IsValueType) return true;
            if (currentType.ToString() == "System.String") return true;
            if (currentType.ToString() == "System.DateTime") return true;
            return false;
        }

        public abstract void Delete(long id);

        protected void ProcessDelete(long id)
        {
            if (id == 0)
                throw new InvalidOperationException("Id of 0 is invalid");

            if (!ProcessExists(id))
            {
                throw new InvalidOperationException("Row for id " + id + " doesn't exist. Can't update.");
            }

            if(deleteCommand==null || !deleteCommand.IsInitialized())
            {
                if (sharedConnection == null)
                    throw new InvalidOperationException("sharedConnection must be set before attempting to process any command");
                deleteCommand = new GenericCommand(factory, sharedConnection, sharedTransaction);
                deleteCommand.AddParameter("@id", DbType.Int64);
            }

            deleteCommand.Set("@id", id);
            string sql = deleteSql.Replace("{table_name}", tableName);
            deleteCommand.ComposeSql(sql);

            //TODO: Execute 'error if not exists'
            
            deleteCommand.ExecuteNonQuery();
        }


        public void Dispose()
        {
            if(needToDispose)
            {
                if (sharedTransaction != null)
                {
                    sharedTransaction.Commit(); 
                    sharedTransaction.Dispose();
                }
                if (sharedConnection != null)
                {
                    if (sharedConnection.State == ConnectionState.Open)
                        sharedConnection.Close();
                    sharedConnection.Dispose();
                }
            }
        }
    }
}
